<!DOCTYPE HTML>
<html>
  <head>
    <title>wll-格式化代码工具</title>
    <meta http-equiv="content-type" content="text/html; charset=utf-8" />
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<script type="text/javascript" src="jquery-1.11.2.min.js"></script> 
	<script src="https://www.jq22.com/jquery/jquery-3.3.1.js"></script>
	<style type="text/css">
		body{font-size: 14px;font-family:"微软雅黑"}
		textarea {width:90% ;height:340px;margin: 4px;border: 1px solid #ccc;padding:6px;}
		label {cursor: pointer;}
		.tmr{height:600px;}
		.sql-column{float:left;width:300px ;margin-left:30px;}
		.sql-column-serach{float:left;width:300px ;margin-left:30px;}
		ul li{list-style:none;float:left;margin-left:10px;padding:5px 10px; border:1px solid #ccc; border-radius:5px; background:#5FB878;color:#fff;cursor:default;}
		ul li:hover{background:#009688;}
		ul li:nth-child(1){
			background-color: #009688;
		}
		.tmr-hien{display:none;}
		.btn{
			display: inline-block;
			height: 38px;
			line-height: 38px;
			padding: 0 18px;
			background-color: #009688;
			color: #fff;
			white-space: nowrap;
			text-align: center;
			font-size: 14px;
			border: none;
			border-radius: 2px;
			cursor: pointer;
		}
		.btn-succes{
			background-color: #1E9FFF;
		}
		#tablename{
			width: 500px;
			padding: 10px;
			
		}
	</style>
  </head>
  
  <body style="padding-top:16px;">
	<div class="sql-column-box">
	<div class="sql-column">
	<h2>字段</h2>
    <textarea id="tm-source">username,
account,
password,
is_delete,
age,
male
	</textarea>
	</div>
	<div class="sql-column-serach">
	<h2>查询条件字段</h2>
    <textarea id="tm-source-search">username,
account,
is_delete
	</textarea>
	</div>
	</div>
	<div style="clear:both;"></div>
	<div>
	<h2>表名</h2>
    <input type="text" id="tablename" value="keke_user" style="padding-left:6px;" title="表名"/> 
	<label><button type="button" onclick="tm_transfter()" class="btn btn-success">开始生成sql</button></label>
	<ul>
		<li>添加sql</li>
		<li>修改sql</li>
		<li>查询sql</li>
		<li>删除sql</li>
		<li>分页查询sql</li>
	</ul>
	<textarea id="tm-result1" class="tmr"></textarea>
	<textarea id="tm-result2" class="tmr tmr-hien"></textarea>
    <textarea id="tm-result3" class="tmr tmr-hien"></textarea>
    <textarea id="tm-result4" class="tmr tmr-hien"></textarea>
    <textarea id="tm-result5" class="tmr tmr-hien"></textarea>
	</div>
    <script type="text/javascript">
		$(function(){
			$("ul li").each(function(index,element){
			    index = index +1;
				$(this).click(function(){
					$(this).css({"background":"#009688"});
					$(this).siblings("li").css({"background":"#5FB878"});
					for(var i=1;i<6;i++){
					if(index == i){
						$("#tm-result"+i).show();
					}else{
						$("#tm-result"+i).hide();
					}
				}
				});
			});
		});
		
    	function tm_transfter(){
    		var source = $.trim($("#tm-source").val().replace(/(\s)*/ig,""));
			var sourceSearch = $.trim($("#tm-source-search").val().replace(/(\s)*/ig,""));
    		if(source!=""){
	    		var tableName = $("#tablename").val();
    			var arr = source.split(",");
    			var arrSearch = sourceSearch.split(",");
				var narr = [];
    			var narr2 = [];
    			var narr3 = [];
    			var narr4 = [];
    			var narr5 = [];
				var narr6 = "";
    			var narr7 = "";
				var narr8 = "";
				
    			for(var i=0;i<arr.length;i++){
    				narr.push("?");
    				narr5.push(columnToProperty2(arr[i]));
    				narr2.push(" #\{"+columnToProperty2(arr[i])+" \}");
    				narr3.push( arr[i]+" =? ");
    				narr4.push(arr[i]+" = #\{"+columnToProperty2(arr[i])+"\}");
					narr6 += "   <if test='"+columnToProperty2(arr[i])+" !=null and "+columnToProperty2(arr[i])+" !=\"\" '> " + arr[i]+" = #\{"+columnToProperty2(arr[i])+"\}, </if> \n";
    			}
				
				for(var i=0;i<arrSearch.length;i++){
    				if(narr7 == ""){
						narr7 += arrSearch[i]+" = #\{"+columnToProperty2(arrSearch[i])+"\} \n";
					}else{
						narr7 += " and " + arrSearch[i]+" = #\{"+columnToProperty2(arrSearch[i])+"\} \n";
					}
		
					if(narr8 == ""){
						narr8 += " <if test='"+columnToProperty2(arrSearch[i])+" !=null and "+columnToProperty2(arrSearch[i])+" !=\"\" '> " + arrSearch[i]+" = #\{"+columnToProperty2(arrSearch[i])+"\} </if> \n";
					}else{
						narr8 += " <if test='"+columnToProperty2(arrSearch[i])+" !=null and "+columnToProperty2(arrSearch[i])+" !=\"\" '> and " + arrSearch[i]+" = #\{"+columnToProperty2(arrSearch[i])+"\} </if> \n";
					}
    			}
    			
				///添加
				var batchaddsource = narr2.toString().replace(/#{/g,"#{item.");
				var batchadd = "INSERT INTO "+tableName+" ("+source+")"+
					" VALUES \n " +
					" <foreach collection=\"list\" separator=\",\" item=\"item\"> \n " +
					" ( " + batchaddsource + ") \n "+
					" </foreach>";
					
    			$("#tm-result1").val("INSERT INTO "+tableName+" ("+source+") VALUES ("+narr.toString()+")\n\n\n INSERT INTO "+tableName+" ("+source+") VALUES ("+narr2.toString()+") \n\n\n\n" + batchadd);
    			
				///修改
				var batchupdatesource = narr2.toString().replace(/#{/g,"#{item.");
				var batchupdate = 
				" <foreach separator=\";\" index=\"index\" item=\"item\" collection=\"list\" close=\"\" open=\"\"> \n   "+ 
				" update "+tableName+" set \n "+ batchupdatesources(source,batchupdatesource) +
				" \n    where id = #{item.id} \n"+
				" </foreach> ";
				
				$("#tm-result2").val("UPDATE "+tableName+" SET "+narr3.toString()+" WHERE ID = ?\n\n\n UPDATE "+tableName+"  SET "+narr4.toString()+"  WHERE  ID = ?\n\n\n UPDATE "+tableName+"  SET \n"+narr6.toString()+"  WHERE  ID = ? \n\n\n " + batchupdate);
				
				////查询
    			$("#tm-result3").val("SELECT "+narr5.toString()+"\n FROM "+tableName+"\n WHERE ID = ?\n ORDER BY CREATE_TIME DESC\n\n\nSELECT "+source+"\n FROM "+tableName+"\n WHERE ID = #\{id\}\n ORDER BY CREATE_TIME DESC");
    		
				///删除
				$("#tm-result4").val("DELETE FROM "+tableName+" WHERE id = ?\n\n\n DELETE FROM "+tableName+" WHERE id = #\{id\}");
				
				////分页查询
    			$("#tm-result5").val("SELECT "+source+"\n FROM "+tableName+"\n WHERE \n " + narr7.toString() + " ORDER BY CREATE_TIME DESC \n limit #{page},#{pageSize} \n\n\n SELECT "+source+"\n FROM "+tableName+"\n WHERE \n " + narr8.toString() + " ORDER BY CREATE_TIME DESC \n limit #{page},#{pageSize} \n\n\n SELECT count(1) FROM "+tableName+"\n WHERE \n " + narr7.toString() + " \n\n\n SELECT count(1) FROM "+tableName+"\n WHERE \n " + narr8.toString());
				
    		}
    	}
    	
    	
    	function batchupdatesources(columns,values) {
			var columnsArr = columns.split(",");
			var valuesArr = values.split(",");
			var lenc = columnsArr.length;
			var lenv = valuesArr.length;
			var ret = "";
			if(lenc == lenv){
				for(var i=0;i<lenc;i++){
					var value = "   "+columnsArr[i] + " = " +valuesArr[i];
					if((i+1) == lenc){
						ret += value;
					}else{
						ret += value +", \n ";
					}
				}
			}
			return ret;
		}
		
    	function columnToProperty2(column) {
    		
            var result = [];
            // 快速检查
            if (column == null || column=="") {
                // 没必要转换
                return "";
            } 
            
            
            if (column.indexOf("_")==-1) {
                // 不含下划线，仅将首字母小写
                return column.substring(0, 1).toLowerCase() + column.substring(1);
            } else {
                // 用下划线将原始字符串分割
                var columns = column.split("_");
                for(var i=0;i<columns.length;i++){
                	var columnSplit =columns[i]; 
                    // 跳过原始字符串中开头、结尾的下换线或双重下划线
                    if (columnSplit=="") {
                        continue;
                    }
                    // 处理真正的驼峰片段
                    if (result.length == 0) {
                        // 第一个驼峰片段，全部字母都小写
                        result.push(columnSplit.toLowerCase());
                    } else {
                        // 其他的驼峰片段，首字母大写
                        result.push(columnSplit.substring(0, 1).toUpperCase());
                        result.push(columnSplit.substring(1).toLowerCase());
                    }
                }
                return result.join("");
            }
        }
    </script>
  </body>
</html>
